Decode remap examples
Decode remapping can be performed in iLoader at the time the database is loaded into Engine or it can be performed using a data engineering tool in (Undefined variable: Variables.ProductName2).
If you choose to perform remapping at the data load stage, the Decode script is specified in the Data Transformation script file. There are two forms of decode remaps, one references data from an external pipe delimited file and the other uses the code and decode values within the Data Transformation script file itself.
Example 1 - Decode remap referencing an external file
The external file can be created in any text editor, for example, Windows Notepad.
The following screen shot shows an example of an external file, used to decode the Occupation column on the Person table.
The script is written in the following format:
DEcode, Full Table Name, Remap Fieldname, Existing Fieldname, External File Name, Value Column Number, Label Column Number
If you are using the Data Transformation wizard, these entries relate to the column fields as follows:
| DEcode | Data Transformation function |
| Full Table Name | Parent Table |
| Remap Fieldname | Short name of decode |
| Short name of decode | Short name of column to decode |
| External File Name | Name of file containing decodes |
| Value Column Number | Column in file containing codes (can refer to any column in the external text file and contains the coded value from the table) |
|
Label Column Number |
Column in file containing decodes (can refer to any column in the external text file and contains the new decoded value) |
Example code:
DEcode, [Demo].[Person], Occupation Decode, Occupation, Occupation Decode.txt, 1,2
Example 2 - Decode remap declared in the Data Transformation script file (inline)
You can use inline decoding to declare the coded and decoded values in the Data Transformation script file itself.
The following example creates the same decode remap as the one used for referencing an external file, called from the Data Transformation script.
|Unknown
DI|Director
HP|Houseperson
MA|Manager
MW|Manual Worker
OW|Office Worker
PR|Professional
RE|Retired
SE|Self Employed
SM|Senior Management
UN|Unemployed
}
| Note: | Each code | decode statement must be on a separate line. |
Any existing decoded values for the specified fields are deleted and replaced by the new values created from the Data Transformation script file.
